package org.zwd.computePrice.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.zwd.computePrice.domain.Crab;
import org.zwd.computePrice.domain.CrabPrice;
import org.zwd.computePrice.domain.Order;
import org.zwd.computePrice.domain.User;
import org.zwd.computePrice.util.DateUtils;

public class OrderDao {

	public void insertOrderItem(Connection conn,String orderid,int cardid,double price,double number,String note){
		PreparedStatement pstmt = null;
		try {
			String sql = "insert into cp_order_crab(orderid,crabid,price,number,note) values(?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, orderid);
			pstmt.setInt(2, cardid);
			pstmt.setDouble(3, price);
			pstmt.setDouble(4, number);
			pstmt.setString(5, note);
			pstmt.executeUpdate();
		} catch (Exception e) {
			throw new RuntimeException(e);
		}finally {
			DBUtils.free(null, pstmt, null);
		}
	}
	
	public void insertOrder(Connection conn,Order order){
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "insert into cp_order(id,time,clientName,totalPay,realPay,totalMaleNum,totalFemaleNum,totalNum,userid,isOrder) values(?,?,?,?,?,?,?,?,?,?)";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, order.getId());
			pstmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			pstmt.setString(3, order.getClientName());
			pstmt.setInt(4, order.getTotalPay());
			pstmt.setInt(5, order.getRealPay());
			pstmt.setDouble(6, order.getTotalMaleNum());
			pstmt.setDouble(7, order.getTotalFemaleNum());
			pstmt.setDouble(8,order.getTotalNumber());
			pstmt.setInt(9, order.getUser().getId());
			pstmt.setInt(10, order.getIsOrder());
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(rs, pstmt, null);
		}
	}
	
	public void deleteOrderByid(Connection conn,String orderid ){
		PreparedStatement pstmt = null;
		try {
			String sql = "delete from cp_order where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, orderid);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(null, pstmt, null);
		}
	}
	
	public void deleteOrderItemByid(Connection conn,String orderid){
		PreparedStatement pstmt = null;
		try {
			String sql = "delete from cp_order_crab where orderid=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, orderid);
			pstmt.executeUpdate();
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(null, pstmt, null);
		}
	}
	
	
	public Order findOrderByOrderId(String orderid,int isOrder){
		Order order = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			conn = DBUtils.getConnection();
			String sql = "select id,time,clientName,totalPay,realPay,totalMaleNum,totalFemaleNum,totalNum,userid,isOrder from cp_order where id=? and isOrder=? order by time desc";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, orderid);
			pstmt.setInt(2, isOrder);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				order = new Order();
				order.setId(rs.getString(1));
				order.setDate(DateUtils.date2String(rs.getDate(2)));
				order.setClientName(rs.getString(3));
				order.setTotalPay(rs.getInt(4));
				order.setRealPay(rs.getInt(5));
				order.setTotalMaleNum(rs.getDouble(6));
				order.setTotalFemaleNum(rs.getDouble(7));
				order.setTotalNumber(rs.getDouble(8));
				User user = new User();
				user.setId(rs.getInt(9));
				order.setUser(user);
				order.setIsOrder(rs.getInt(10));
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(rs, pstmt, conn);
		}
		return order;
	}
	
	public List<Crab> findCrabByOrderId(String orderid){
		CrabDao crabDao = new CrabDao();
		
		List<Crab> crabList = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			crabList = new ArrayList<Crab>();
			conn = DBUtils.getConnection();
			String sql = "select crabid,price,number,note from cp_order_crab where orderid=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, orderid);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				Crab crab = crabDao.findCrabById(rs.getInt(1));
				CrabPrice crabPrice = new CrabPrice();
				crabPrice.setPrice(rs.getDouble(2));
				crab.setCrabPrice(crabPrice);
				crab.setNumber(rs.getDouble(3));
				crab.setNote(rs.getString(4));
				crabList.add(crab);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(rs, pstmt, conn);
		}
		return crabList;
		
	}
	
	public List<String> findOrderIdByTime(String startTime,String endTime,int isOrder){
		List<String> idList = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			idList = new ArrayList<String>();
			conn = DBUtils.getConnection();
			String sql = "select id from cp_order where UNIX_TIMESTAMP(time) between UNIX_TIMESTAMP(?) and UNIX_TIMESTAMP(?) and isOrder=? order by time desc";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,startTime);
			pstmt.setString(2,endTime);
			pstmt.setInt(3, isOrder);
			//System.out.println(pstmt);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				String orderid = rs.getString(1);
				idList.add(orderid);
			}
			//System.out.println(idList);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(rs, pstmt, conn);
		}
		return idList;
	}
	
	public List<String> findOrderIdByName(String name,int isOrder){
		List<String> idList = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		try {
			idList = new ArrayList<String>();
			conn = DBUtils.getConnection();
			String sql = "select id from cp_order where clientName = ? and isOrder=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,name);
			pstmt.setInt(2, isOrder);
			//System.out.println(pstmt);
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				String orderid = rs.getString(1);
				idList.add(orderid);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(rs, pstmt, conn);
		}
		return idList;
	}
	
	public void updateRealPayById(String orderid,int realPay){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DBUtils.getConnection();
			String sql = "update cp_order set realPay=? where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, realPay);
			pstmt.setString(2, orderid);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(null, pstmt, conn);
		}
	}
	
	public void updateOrder(Connection conn,Order order){
		PreparedStatement pstmt = null;
		
		try {
			String sql = "update cp_order set clientName=?,totalPay=?,realPay=?,totalMaleNum=?,totalFemaleNum=?,totalNum=? where id=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, order.getClientName());
			pstmt.setInt(2, order.getTotalPay());
			pstmt.setInt(3, order.getRealPay());
			pstmt.setDouble(4, order.getTotalMaleNum());
			pstmt.setDouble(5, order.getTotalFemaleNum());
			pstmt.setDouble(6, order.getTotalNumber());
			pstmt.setString(7, order.getId());
			pstmt.executeUpdate(); 
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(null, pstmt, null);
		}
	}
	
	public void updateOrderItem(Connection conn,String orderid,Crab crab){
		PreparedStatement pstmt = null;
		try {
			String sql = "update cp_order_crab set price=?,number=?,note=? where orderid=? and crabid=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, crab.getCrabPrice().getPrice());
			pstmt.setDouble(2, crab.getNumber());
			pstmt.setString(3, crab.getNote());
			pstmt.setString(4, orderid);
			pstmt.setInt(5, crab.getId());
			pstmt.executeUpdate(); 
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
		finally {
			DBUtils.free(null, pstmt, null);
		}
	}

}
